\! gs_ktool -d all
\! gs_ktool -g

DROP CLIENT MASTER KEY IF EXISTS in_out_cmk CASCADE;
CREATE CLIENT MASTER KEY in_out_cmk WITH ( KEY_STORE = gs_ktool , KEY_PATH = "gs_ktool/1" , ALGORITHM = AES_256_CBC);
CREATE COLUMN ENCRYPTION KEY in_out_cek WITH VALUES (CLIENT_MASTER_KEY = in_out_cmk, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);

CREATE TABLE t_processed (name text, val INT ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = in_out_cek, ENCRYPTION_TYPE = DETERMINISTIC), val2 INT);

insert into t_processed values('one',1,10),('two',2,20),('three',3,30),('four',4,40),('five',5,50),('six',6,60),('seven',7,70),('eight',8,80),('nine',9,90),('ten',10,100);

CREATE TABLE t_processed_b (name text, val text ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = in_out_cek, ENCRYPTION_TYPE = DETERMINISTIC), val2 INT);
INSERT INTO t_processed_b VALUES('name1', 'one', 10),('name2', 'two', 20),('name3', 'three', 30),('name4', 'four', 40),('name5', 'five', 50),('name6', 'six', 60),('name7', 'seven', 70),('name8', 'eight', 80),('name9', 'nine', 90),('name10', 'ten', 100);

CREATE OR REPLACE FUNCTION f_processed_in_out_1param( out1 OUT int,in1 int) AS 'SELECT val from t_processed  where val = in1 LIMIT 1' LANGUAGE SQL; 

CREATE OR REPLACE FUNCTION f_processed_in_out(out1 OUT int,in1 int,  out2 OUT int) AS 'SELECT val, val2 from t_processed where val = in1 LIMIT 1' LANGUAGE SQL;

CREATE OR REPLACE FUNCTION f_processed_in_out_b(out1 OUT text, out2 OUT int,in1 text, in2 text) AS 'SELECT val, val2 from t_processed_b where val = in1 or name = in2 LIMIT 1' LANGUAGE SQL;

CREATE OR REPLACE FUNCTION f_processed_in_out_plpgsql(in1 int, out out1 int, in2 int, out out2 int) 
as $$ 
begin 
  select val, val2 INTO out1, out2 from t_processed where val = in2 or val = in1 limit 1; 
end;$$ 
LANGUAGE plpgsql; 

CREATE OR REPLACE FUNCTION f_processed_in_out_plpgsql2(out out1 t_processed.val%TYPE, out out2 t_processed.val%TYPE, in1 t_processed.val%TYPE) 
as $$ 
begin 
  select val, val2 INTO out1, out2 from t_processed where val = in1 limit 1; 
end;$$ 
LANGUAGE plpgsql; 

CREATE OR REPLACE FUNCTION f_processed_in_out_aliases_plpgsql(out out1 int, in1 int,out out2 int) as  
$BODY$ 
DECLARE  
 val1 ALIAS FOR out1; 
 input_p ALIAS for in1;
begin 
  select val, val2 INTO val1, out2 from t_processed where val = input_p; 
end; 
$BODY$
LANGUAGE plpgsql; 
select proname, prorettype, proallargtypes, prorettype_orig, proallargtypes_orig FROM pg_proc LEFT JOIN gs_encrypted_proc ON pg_proc.
Oid = gs_encrypted_proc.func_id WHERE proname IN ('f_processed_in_out', 'f_processed_in_out_plpgsql', 'f_processed_in_out_plpgsql2', 'f_processed_in_out_aliases_plpgsql', 'f_processed_in_out_1param') ORDER BY proname;
SELECT f_processed_in_out_1param(2);
SELECT f_processed_in_out(5);
SELECT f_processed_in_out_b('ten','name70');
SELECT f_processed_in_out_plpgsql(17,3);
SELECT f_processed_in_out_plpgsql2(6);
SELECT f_processed_in_out_aliases_plpgsql(4);
DROP FUNCTION f_processed_in_out_1param;
DROP FUNCTION f_processed_in_out(int);
DROP FUNCTION f_processed_in_out_b;
DROP FUNCTION f_processed_in_out_plpgsql;
DROP FUNCTION f_processed_in_out_plpgsql2;
DROP FUNCTION f_processed_in_out_aliases_plpgsql;
DROP TABLE t_processed CASCADE;
DROP TABLE t_processed_b CASCADE;
DROP COLUMN ENCRYPTION KEY in_out_cek;
DROP CLIENT MASTER KEY in_out_cmk;
\! gs_ktool -d all